package com.xhh.nphm.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xhh.nphm.common.core.domain.QueryConditionVo;
import com.xhh.nphm.system.domain.MmailWorkForm;
import com.xhh.nphm.system.domain.TwfSendBackEty;
import com.xhh.nphm.system.domain.TworkFormBaseEty;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.StatementType;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.stereotype.Repository;

import java.io.Serializable;
import java.util.List;
import java.util.Map;

@Repository
public interface WorkFormBaseDao extends BaseMapper<TworkFormBaseEty> {

    @Select("SELECT pkId,originFlag,getDealPk,callId,tapeId,fmAreaNo,fmAreaName,fromName,fromSex,fmAddress,fromTelA," +
            "fromTelB,fromTelC,fmEmail,fmCerName,fmCerNo,openFrom,fmContent,dfmClaNo,dfmClaName,dcntAname,dcntAcode," +
            "dcntBname,dcntBcode,wsCode,wsKey,fmAttachP,fmAttachN,baseInt,baseStr,workFormNo,wfClaNo,wfClaName," +
            "openX,wfLimit,wfDefer,wfRemark,doverTime,doverDesc,reportFlag,reportTo,iptTime,wsTopic,wsUFlag,wsSpare," +
            "iptWorkNo,iptWorker,setProId,setProName,crtStepId,crtStepName,crtStepTime,visitFlag,recordFlag," +
            "recordTime,recUserNo,recUserName,isDel,spareInt,spareString,flagInt,flagString,fkint,fkstring,fmSpare," +
            "commitTime,mark,specialLevel,keyWord,accSubject " +
            "FROM tWorkFormBase " +
            "WHERE pkId=#{id} ")
    TworkFormBaseEty selectById(@Param("id") Serializable id);

    /**
     * 根据来电记录id 查工单
     * @param getDealPk
     * @return
     */
    @Select("select * from tWorkFormBase where GetDealPK=#{getDealPk} and IsDel = 0")
    TworkFormBaseEty selectByGetDealPk(Integer getDealPk);

    /**
     * 根据主键 查工单
     * @param pkid
     * @return
     */
    @Select("select * from tWorkFormBase where PkId=#{pkid} and IsDel = 0")
    TworkFormBaseEty selectByPkId(Integer pkid);

    /**
     * 查询电话工单初稿列表
     * @return 电话工单初稿列表
     */
    @Select("SELECT PkId,getDealPK,WorkFormNO,SetProID,WsTopic,FmAreaName,FromName,FromTelA,DFmClaName,convert(varchar(19),IptTime,20) as IptTime,OpenFrom,Mark,convert(varchar(19),WfLimit,20) AS WfLimit,CrtStepName FROM tWorkFormBase " +
            "WHERE IsDel = 0 AND CrtStepID IN (100,103) " +
            "[[and WorkFormNO = #{workFormNo}]] " +
            "[[and FromName =#{fromName}]] " +
            "[[and FromTelA =#{fromTelA}]] " +
            "[[and FmAreaName =#{fmAreaName}]]" +
            "ORDER BY PkId desc")
    List<TworkFormBaseEty> findTwfDraftList(TworkFormBaseEty twfb);

    @Select("SELECT COUNT(pkId) FROM tWorkFormBase WHERE YEAR(iptTime)=#{fpYear}  AND MONTH(iptTime)=#{fpMth} AND DAY(iptTime)=#{fpDay} AND LEN(workFormNo)>0")
    String selectByDate(@Param("fpYear") String fpYear, @Param("fpMth")String fpMth, @Param("fpDay")String fpDay);

    /**
     * 查找网评不满意的工单
     */
    @Select("select PkId," +
            "WorkFormNO,SetProID,WsTopic," +
            "FmAreaName,FromName,FromTelA," +
            "DFmClaName,convert(varchar(19),IptTime,20) as fmTime," +
            "OpenFrom,convert(varchar(10),WfLimit,20) as lmtDate," +
            "CrtStepName,tWorkFormBase.PkId as myOP," +
            "(select top 1 SUserName from tWfSendBack where tWorkFormBase.PkId=tWfSendBack.WfId and isDel=0 and SendState = 40 and SeqCode='10' and SUserName!='') as SUserName," +
            "(select top 1 OpUserNo from tWorkFormLog where tWorkFormLog.OpAction = '审核退回工单' and WsDesc like '%(通过)%' and tWorkFormBase.PkId = tWorkFormLog.WfId) as OpUserNo from tWorkFormBase " +
            "where CrtStepID in (120) and IsDel=0 and CrtStepName='网评不满意' " +
            "[[and WorkFormNO = #{workFormNo}]] " +
            "[[and FromName =#{fromName}]] " +
            "[[and FromTelA =#{fromTelA}]] " +
            "[[and FmAreaName =#{fmAreaName}]]" +
            "order by mark desc,pkid desc")
    List<TworkFormBaseEty> findBadCommentTwfbList(TworkFormBaseEty twfb);

    /**
     * 已回访工单列表数据
     * @param tworkFormBaseEty
     * @return
     */
    @Select("select PkId,GetDealPK,WorkFormNO,SetProID,WsTopic,FmAreaName,FromName,FromTelA,DFmClaName,OpenFrom,convert(varchar(10),DOverTime,20) as lmtDate,VisitFlag from tWorkFormBase where CrtStepID in (901,905) and VisitFlag in (20,21,22,31) and IsDel=0 " +
            "[[and WorkFormNO = #{workFormNo}]] " +
            "[[and FromName =#{fromName}]] " +
            "[[and FmAreaName =#{fmAreaName}]] " +
            "[[and FromTelA =#{fromTelA}]]")
    List<TworkFormBaseEty> backOverListJson(TworkFormBaseEty tworkFormBaseEty);

    @Select("select PkId,getDealPk,workFormNo,setProId,wstopic,fmAreaName,fromName,fromTelA,dfmClaName, iptTime,openX,wfLimit,crtStepName,PkId as opScan from tWorkFormBase where PkId!=0 and IsDel=0 " +
            "[[and workFormNo = #{workFormNo}]] " +
            "[[and wsCode = #{wsCode}]] "+
            "[[and fromName =#{fromName}]]" +
            "[[and fmContent =#{fmContent}]]" +
            "[[and fromTelA =#{fromTelA}]]"+
            "order by PkId desc")
    List<TworkFormBaseEty> telWFAll(TworkFormBaseEty tworkFormBaseEty);

    /**
     * 被退回电话工单列表数据
     * @param tworkFormBaseEty
     * @return
     */
    @Select("select PkId as myPkVal,WorkFormNO,SetProID,WsTopic,FmAreaName,FromName,FromTelA,DFmClaName,convert(varchar(19),IptTime,20) as fmTime,OpenFrom,convert(varchar(10),WfLimit,20) as lmtDate,CrtStepName,tWorkFormBase.PkId as myOP,(select top 1 SUserName from tWfSendBack where tWorkFormBase.PkId=tWfSendBack.WfId and isDel=0 and SendState = 40 and SeqCode='10' and SUserName!='') as SUserName,(select top 1 OpUserNo from tWorkFormLog where tWorkFormLog.OpAction = '审核退回工单' and WsDesc like '%(通过)%' and tWorkFormBase.PkId = tWorkFormLog.WfId) as OpUserNo from tWorkFormBase where CrtStepID in (201,251,401) and IsDel=0  order by mark desc, PkId desc")
    List<TworkFormBaseEty> backFlowJson(TworkFormBaseEty tworkFormBaseEty);

    /**
     * 待审办理结果列表数据
     * @param tworkFormBaseEty
     * @return
     */
    @Select("select PkId,GetDealPK,WorkFormNO,SetProID,WsTopic,FmAreaName,FromName,FromTelA,DFmClaName," +
            "convert(varchar(19),IptTime,20) as fmTime,OpenFrom,convert(varchar(10),WfLimit,20) as lmtDate,CrtStepID,CrtStepName" +
            " from tWorkFormBase where CrtStepID in (810,820) and IsDel=0 " +
            "[[and workFormNo = #{workFormNo}]] " +
            "[[and wsCode = #{wsCode}]] " +
            "[[and fromTelA =#{fromTelA}]] " +
            "[[and fromName =#{fromName}]] " +
            "[[and fmAreaName =#{fmAreaName}]] " +
            "[[and dcntAname =#{dcntAname}]] order by mark desc, PkId desc")
    List<TworkFormBaseEty> replyWaitJson(TworkFormBaseEty tworkFormBaseEty);

    /**
     * 查询重点诉求推荐列表数据
     *
     * @param twfSendBackEty 拟派工单
     * @return 重点诉求推荐列表数据
     */
    @Select("SELECT * FROM tWorkFormBase twf WHERE (SELECT count(0) FROM tWfSendBack WHERE WfId = twf.PkId) >= 3;")
    List<Map<String, Object>> keyTwfRecommendListJson(TwfSendBackEty twfSendBackEty);

    /**
     * 根据热度排序查询被诉主体列表
     *
     * @return
     */
    @Select("SELECT accSubject FROM tWorkFormBase GROUP BY accSubject order by COUNT(accSubject) desc")
    List<String> hotSpotAccSubjects();

    /**
     * 查询热点诉求推荐列表数据
     *
     * @param tworkFormBaseEty 根据工单实体(被诉主体)查询
     * @return
     */
    @Select("SELECT * FROM tWorkFormBase twf WHERE accSubject IN(" +
            "   SELECT TOP 1 accSubject " +
            "   FROM tWorkFormBase where isDel = 0 [[and accSubject = #{accSubject}]]" +
            "   GROUP BY accSubject" +
            "   ORDER BY COUNT(accSubject) desc" +
            ")")
    List<Map<String, Object>> hotSpotRecommendListJson(TworkFormBaseEty tworkFormBaseEty);

    /**
     * 查询近期热点事件推送列表数据
     *
     * @param tworkFormBaseEty 查询条件
     * @return 列表数据
     */
    @Select("SELECT * FROM tWorkFormBase twf " +
            "WHERE IsDel = 0 AND DFmClaNo = '10' " +
            "AND DCntACode IN(" +
            "SELECT DCntACode FROM tWorkFormBase as twf " +
            "WHERE IsDel = 0 AND DFmClaNo = '10' " +
            "AND DATEDIFF(dd,twf.IptTime,GETDATE()) &lt; 30 " +
            "GROUP BY DCntACode " +
            "HAVING count(0) &gt;= 5 [[AND DCntACode = #{dcntAcode}]]" +
            ")")
    List<Map<String, Object>> nearHotSpotTwfRecommendListJson(TworkFormBaseEty tworkFormBaseEty);
    @Select("select pkId,originFK,workFormNo,setProId,fromTopic,fmAreaName,fromName,fromTel,dfmClaName, iptTime,openX,wfLimit,crtStepName,PkId as opScan from mMailWorkForm where PkId!=0 and IsDel=0 " +
            "[[and workFormNo = #{workFormNo}]] " +
            "[[and wsCode = #{wsCode}]] "+
            "[[and fromName =#{fromName}]]" +
            "[[and fmContent =#{fmContent}]]" +
            "[[and fromTel =#{fromTel}]]"+
            "order by PkId desc")
    List<MmailWorkForm> mailWFList(MmailWorkForm mmailWorkForm);

    /**
     * 查询近期典型事件推送列表数据
     *
     * @param tworkFormBaseEty 查询条件
     * @return 列表数据
     */
    @Select("SELECT * FROM tWorkFormBase WHERE IsDel = 0" +
            " AND FmAddress IN(SELECT FmAddress FROM tWorkFormBase" +
            " WHERE IsDel = 0 AND DATEDIFF(dd, IptTime,GETDATE()) < 30 AND FmAddress != '' AND DCntACode != '' GROUP BY FmAddress,DCntACode" +
            " HAVING COUNT(0) > 5) AND DCntACode IN(SELECT DCntACode FROM tWorkFormBase WHERE IsDel = 0 AND DATEDIFF(dd, IptTime,GETDATE()) < 30 " +
            "AND FmAddress != '' AND DCntACode != '' GROUP BY FmAddress,DCntACode HAVING COUNT(0) > 5)")
    List<Map<String, Object>> nearTypicalTwfRecommendListJson(TworkFormBaseEty tworkFormBaseEty);

    /**
     * 查询特殊诉求列表数据
     *
     * @param tworkFormBaseEty 查询条件
     * @return 列表数据
     */
    @Select("SELECT * FROM tWorkFormBase WHERE IsDel = 0 AND specialLevel = 1")
    List<Map<String, Object>> specialTwfRecommendListJson(TworkFormBaseEty tworkFormBaseEty);

    /**
     * 查询典型诉求列表数据
     *
     * @param tworkFormBaseEty 查询条件
     * @return 列表数据
     */
    @Select("SELECT * FROM tWorkFormBase WHERE IsDel = 0 AND specialLevel >= 2")
    List<Map<String, Object>> typicalTwfRecommendListJson(TworkFormBaseEty tworkFormBaseEty);

    /**
     * 待回访电话工单列表数据
     * @param tworkFormBaseEty
     * @return
     */
    @Select("select PkId,GetDealPK,WorkFormNO,SetProID,WsTopic,FmAreaName,FromName,FromTelA,DFmClaName,convert(varchar(20),IptTime,20) as IptTime,convert(varchar(10),WfLimit,20) as WfLimit,OpenFrom,convert(varchar(20),DOverTime,20) as DOverTime from tWorkFormBase where  CrtStepID=900 and IsDel=0 [[and WorkFormNO = #{workFormNo}]][[and FromTelA = #{fromTelA}]][[and FromName = #{fromName}]][[and FmAreaName = #{fmAreaName}]] order by DOverTime desc")
    List<Map<String,Object>> backWaitListJson(TworkFormBaseEty tworkFormBaseEty);

    /**
     * 根据事发地址查询电话工单
     * @param fmAddress
     * @return
     */
    @Select("select PkId,GetDealPK,WorkFormNo,WsTopic,WsCode,IptTime,FmContent,CrtStepName from tWorkFormBase where  FmAddress =#{fmAddress} and IsDel=0  order by PkId desc")
    List<TworkFormBaseEty> telfmAddressJson(String fmAddress);

    /**
     * 根据被诉主体查询电话工单
     * @param accSubject
     * @return
     */
    @Select("select PkId,GetDealPK,WorkFormNo,WsTopic,WsCode,IptTime,FmContent,CrtStepName from tWorkFormBase where  accSubject =#{accSubject} and IsDel=0  order by DOverTime desc")
    List<TworkFormBaseEty> telaccSubjectJson(String accSubject);

    /**
     * 查询电话工单抄送件列表数据
     *
     * @param tworkFormBaseEty 查询条件
     * @param deptId           部门编号
     * @return 列表数据
     */
    @Select("select tw.PkId,getDealPk,workFormNo,setProId,wstopic,fmAreaName,fromName,fromTelA,dfmClaName, iptTime,openX,wfLimit,crtStepName from tWorkFormBase as tw,tWorkcsbase as tc" +
            " where tw.PkId!=0 and tw.IsDel=0 and tw.PkId = tc.wfId AND tc.deptId = #{deptId}" +
            "[[and workFormNo = #{twf.workFormNo}]] " +
            "[[and wsCode = #{twf.wsCode}]] "+
            "[[and fromName =#{twf.fromName}]]" +
            "[[and fmContent =#{twf.fmContent}]]" +
            "[[and fromTelA =#{twf.fromTelA}]]"+
            "order by PkId desc")
    List<TworkFormBaseEty> telCopyList(@Param("twf") TworkFormBaseEty tworkFormBaseEty,@Param("deptId") Long deptId);

    /**
     * 根据用户角色编号和部门编号 查询全区实时满意情况
     *
     * @param params 参数
     * @return List<Map>
     */
    @Select("{call psTelTotalHandleState(#{roleId,mode=IN,jdbcType=VARCHAR},#{deptId,mode=IN,jdbcType=VARCHAR})}")
    @Options(statementType = StatementType.CALLABLE)
    List<Map> psTelTotalHandleState(Map<String, String> params);

    /**
     * 根据用户角色编号和部门编号 查询全区实时满意情况
     *
     * @param params 参数
     * @return List<Map>
     */
    @Select("{call psTelTotalHandleState_gxq(#{roleId,mode=IN,jdbcType=VARCHAR},#{deptId,mode=IN,jdbcType=VARCHAR})}")
    @Options(statementType = StatementType.CALLABLE)
    List<Map> psTelTotalHandleState_gxq(Map<String, String> params);

    /**
     * 查看已删除工单详情
     * @param pkId
     * @return
     */
    @Select("select * from tWorkFormBase where PkId=#{pkId}")
    TworkFormBaseEty getByPkId(Integer pkId);

    /**
     * 根据工单号查看工单详情
     * @param workNo
     * @return
     */
    @Select("select * from tWorkFormBase where WorkFormNo=#{workNo}")
    List<TworkFormBaseEty> getByWorkNo(String workNo);

    /**
     * 查询电话紧急联系调整
     * @return
     */
    @Select("select PkId,WorkFormNO,SetProID,WsTopic,FmAreaName,FromName,FromTelA,DFmClaName,convert(varchar(19),IptTime,20) as fmTime,OpenFrom,Openx," +
            "convert(varchar(10),WfLimit,20) as WfLimit,CrtStepName,PkId as myOP,Mark from tWorkFormBase where CrtStepID in (400,401,810) and IsDel=0 " +
            "[[and wsCode=#{wsCode}]] [[and workFormNO=#{workFormNO}]] [[and fromName=#{fromName}]] [[and fromTelA=#{fromTelA}]] [[and dcntAName=#{dcntAName}]]" +
            " order by mark desc, PkId desc")
    List<Map<String,Object>> wfListBankName(QueryConditionVo qc);
}
